﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using HREDU.Hoang_Tuan.Classes;
using HREDU.Hoang_Tuan;
using System.Globalization;

namespace HREDU.Hoang_Tuan
{
    public partial class frmBangLuong : Form
    {
        private const int MINVALUE = 0; //giá trị hệ số nhỏ nhất có thể
        private const int MAXVALUE = 50; //giá trị hệ số lớn nhất có thể
        private const int FIRSTBAC = 4; //Cho biet vi tri column dau tien la cua Bac luong

        //private DataGridComboBoxColumn dataGridComboBoxColumnActivity;
        //Bien luu tru bang crosstab Ngach Bac Luong
        private DataSet dsBangLuong = null;
        public Form fCallForm = null;
        int luongcb = -1;

        public frmBangLuong()
        {
            InitializeComponent();
            btnChon.Visible = false;
        }

        public frmBangLuong(Form frm)
        {
            InitializeComponent();
            fCallForm = frm;

            txtLuongCB.ReadOnly = true;
            numericUpDownSoNam.Enabled= false;
            numericUpDownTNVK_1.Enabled = false;
            numericUpDownTNVK_2.Enabled = false;
            btnThemMoi.Visible = false;
            btnCapNhat.Visible = false;
            btnXoa.Visible = false;
            btnChon.Location = new Point(526, 313);
            BANGLUONGDataGridView.ReadOnly = true;
        }

        private void btnCapNhat_Click(object sender, EventArgs e)
        {
            string strUpdate = "";
            string strInsert = "";

            //Chuyen doi lai tu ngay kieu Viet Nam sang ngay kieu en-US
            CultureInfo cul = GeneralCode.GetCultureInfoUS();
            NumberFormatInfo nfi = cul.NumberFormat;
            
            //CAP NHAT THAM SO
            CTHAMSOCollection.UpdateGiaTriSo(CTHAMSOCollection.parLuongCB, luongcb);
            //CTHAMSOCollection.UpdateGiaTriSo(CTHAMSOCollection.parSNGB, (int)numericUpDownSoNam.Value);
            //CTHAMSOCollection.UpdateGiaTriSo(CTHAMSOCollection.parTNVK_1, (int)numericUpDownTNVK_1.Value);
            //CTHAMSOCollection.UpdateGiaTriSo(CTHAMSOCollection.parTNVK_2, (int)numericUpDownTNVK_2.Value);

            //CAP NHAT NHOM NGACH
            strUpdate = "update NHOMNGACH set ";
            strUpdate += " sonamgiubac = " + (int)numericUpDownSoNam.Value;
            strUpdate += ", tnvk_1 = " + (int)numericUpDownTNVK_1.Value;
            strUpdate += ", tnvk_2 = " + (int)numericUpDownTNVK_2.Value;
            strUpdate += " where id_nhomngach = " + cmbNhomNgach.SelectedValue;
            //Thuc hien cap nhat cho moi ngach luong
            if (MITI.BaseDB.Instance.ExecSql_NoneQuery(strUpdate) <= 0)
            {
                MessageBox.Show("Cập nhật không thành công.");
                return;
            }


            //Voi moi dong, ta lan luot update cac he so o tung column bac luong cua tung dong
            //Vòng lặp đầu tiên lấy số ngạch đang có trong DataGridView (kể cả thêm mới)
            for (int row = 0; row < dsBangLuong.Tables[0].Rows.Count; row++)
            {
                //Neu id_ngach da co roi thi cap nhat
                if (int.Parse(dsBangLuong.Tables[0].Rows[row]["id_ngach"].ToString()) != -1)
                {
                    //Update Ngach Luong va Ma so ngach neu co thay doi
                    strUpdate = " update NGACH set ";
                    strUpdate += " id_nhomngach = " + dsBangLuong.Tables[0].Rows[row]["id_nhomngach"].ToString() + ",";
                    strUpdate += " maso = '" + dsBangLuong.Tables[0].Rows[row]["maso"].ToString() + "',";
                    strUpdate += " ten_ngach = N'" + dsBangLuong.Tables[0].Rows[row]["ten_ngach"].ToString() + "'";
                    strUpdate += " where id_ngach = " + dsBangLuong.Tables[0].Rows[row]["id_ngach"].ToString();
                    //Vòng lặp thứ 2: với mỗi column của bậc luong, ta lấy giá trị các hệ số để cập nhật
                    for (int i = FIRSTBAC; i < dsBangLuong.Tables[0].Columns.Count; i++)
                    {
                        float value;
                        try
                        {
                            value = float.Parse(dsBangLuong.Tables[0].Rows[row][i].ToString());
                        }
                        catch
                        {
                            value = 0;
                        }

                        //Bao loi neu gia tri nhap < MINVALUE hoặc >= MAXVALUE
                        if (value < MINVALUE || value >= MAXVALUE)
                        {
                            string msg = "Giá trị nhập phải lớn hơn " + MINVALUE.ToString() + " và nhỏ hơn " + MAXVALUE.ToString() + ".";
                            MessageBox.Show(msg);
                            BANGLUONGDataGridView.ClearSelection();
                            BANGLUONGDataGridView.Rows[row].Cells[i].Selected = true;
                            return;
                        }

                        //update he so luong voi dieu kien ma ngach va ma bac luong
                        strUpdate += " update NBL set ";
                        //strUpdate += " heso = cast('" + dsBangLuong.Tables[0].Rows[row][i].ToString() + "' as float)";
                        strUpdate += " heso = " + (value == 0 ? "null" : " cast('" + value.ToString("N", nfi) + "' as float)");
                        strUpdate += " where id_ngach = " + dsBangLuong.Tables[0].Rows[row]["id_ngach"].ToString();
                        strUpdate += " and id_bac = " + dsBangLuong.Tables[0].Columns[i].ColumnName.ToString();

                    } //End For
                    //Thuc hien cap nhat cho moi ngach luong
                    if (MITI.BaseDB.Instance.ExecSql_NoneQuery(strUpdate) <= 0)
                        MessageBox.Show("Cập nhật không thành công.");
                } //End if
                //neu id_ngach chua co thi them moi vao CSDL
                else
                {
                    //kiem tra ma so ngach them vao co chua?
                    string strCheck = "select * from NGACH where";
                    strCheck += " maso = N'" + dsBangLuong.Tables[0].Rows[row]["maso"].ToString() + "'";
                    DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strCheck);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        MessageBox.Show("Đã có mã số này rồi.");
                        return;
                    }

                    //them id_ngach moi
                    //sau do gan lai vao datatable
                    strInsert = " insert into NGACH values(";
                    strInsert += dsBangLuong.Tables[0].Rows[row]["id_nhomngach"].ToString() + ",";
                    strInsert += "'" + dsBangLuong.Tables[0].Rows[row]["maso"].ToString() + "',";
                    strInsert += "N'" + dsBangLuong.Tables[0].Rows[row]["ten_ngach"].ToString() + "')";
                    
                    MITI.BaseDB.Instance.ExecSql_NoneQuery(strInsert);

                    //Lay id_ngach vua insert vao datatable
                    strInsert = "select id_ngach from NGACH where";
                    strInsert += " maso = '" + dsBangLuong.Tables[0].Rows[row]["maso"].ToString() + "' and";
                    strInsert += " ten_ngach = N'" + dsBangLuong.Tables[0].Rows[row]["ten_ngach"].ToString() + "'";
                    dsBangLuong.Tables[0].Rows[row]["id_ngach"] = MITI.BaseDB.Instance.ExecSql_DataValue(strInsert);

                    //Tao truoc cac dong bac luong trong bang NBL voi id_ngach vua tao
                    strInsert = "";
                    for (int i = FIRSTBAC; i < dsBangLuong.Tables[0].Columns.Count; i++)
                    {
                        strInsert += " insert into NBL values(";
                        strInsert += dsBangLuong.Tables[0].Rows[row]["id_ngach"].ToString() + ",";
                        strInsert += dsBangLuong.Tables[0].Columns[i].ColumnName.ToString() + ",";
                        strInsert += " null)";
                    }
                    MITI.BaseDB.Instance.ExecSql_NoneQuery(strInsert);

                    //Reset string SQL = ""
                    strInsert = "";

                    //Vòng lặp thứ 2: với mỗi column của bậc luong, ta lấy giá trị các hệ số để cập nhật
                    for (int i = FIRSTBAC; i < dsBangLuong.Tables[0].Columns.Count; i++)
                    {
                        float value;
                        try
                        {
                            value = float.Parse(dsBangLuong.Tables[0].Rows[row][i].ToString());
                        }
                        catch
                        {
                            value = 0;
                        }

                        //Bao loi neu gia tri nhap < MINVALUE hoặc >= MAXVALUE
                        if (value < MINVALUE || value >= MAXVALUE)
                        {
                            string msg = "Giá trị nhập phải lớn hơn " + MINVALUE.ToString() + " và nhỏ hơn " + MAXVALUE.ToString() + ".";
                            MessageBox.Show(msg);
                            BANGLUONGDataGridView.ClearSelection();
                            BANGLUONGDataGridView.Rows[row].Cells[i].Selected = true;
                            return;
                        }

                        //update he so luong voi dieu kien ma ngach va ma bac luong
                        strInsert += " update NBL set ";
                        strInsert += " heso = " + (value == 0 ? "null" : " cast('" + value.ToString("N", nfi) + "' as float)");
                        strInsert += " where id_ngach = " + dsBangLuong.Tables[0].Rows[row]["id_ngach"].ToString();
                        strInsert += " and id_bac = " + dsBangLuong.Tables[0].Columns[i].ColumnName.ToString();
                    } //End for
                    //Thuc hien insert cho moi ngach luong moi them
                    if (MITI.BaseDB.Instance.ExecSql_NoneQuery(strInsert) <= 0)
                    {
                        MessageBox.Show("Cập nhật không thành công.");
                        return;
                    }

                }
            }
            MessageBox.Show("Cập nhật thành công.");
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        //Ham nay dung de thay the MaKho textbox column thanh combobox column
        private void AddComboBoxColumns_NhomNgach()
        {
            DataGridViewComboBoxColumn comboboxColumn = new DataGridViewComboBoxColumn();
            comboboxColumn.Name = "id_nhomngach";
            comboboxColumn.Frozen = true;
            comboboxColumn.DataPropertyName = "id_nhomngach";
            comboboxColumn.HeaderText = "Nhóm Ngạch";
            comboboxColumn.FlatStyle = FlatStyle.Flat;
            comboboxColumn.DropDownWidth = 150;
            comboboxColumn.Width = BANGLUONGDataGridView.Columns["id_nhomngach"].Width + 20;
            comboboxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
            // Bind activities to comboBoxActivities
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet("select * from NHOMNGACH");
            comboboxColumn.DataSource = ds.Tables[0];
            comboboxColumn.DisplayMember = "ten_nhomngach";
            comboboxColumn.ValueMember = "id_nhomngach";
            try
            {
                int index = BANGLUONGDataGridView.Columns["id_nhomngach"].Index;
                BANGLUONGDataGridView.Columns.Remove("id_nhomngach");
                BANGLUONGDataGridView.Columns.Insert(index, comboboxColumn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        
    
        public void CreateDataSetDPNX(ref DataSet dsDataSet)
        {
         try {
             DataColumn myDataColumn;
            
             DataTable myDataTable = new DataTable("BangLuong");

             //Add column id ngach
             myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.String");
             myDataColumn.ColumnName = "id_ngach";
             myDataTable.Columns.Add(myDataColumn);

             //Add column ten ngach
             myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.String");
             myDataColumn.ColumnName = "ten_ngach";
             myDataTable.Columns.Add(myDataColumn);

             //Add column ma so cua ngach
             myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.String");
             myDataColumn.ColumnName = "maso";
             myDataTable.Columns.Add(myDataColumn);

             //Add column nhom ngach
             myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.Int32");
             myDataColumn.ColumnName = "id_nhomngach";
             myDataTable.Columns.Add(myDataColumn);


             //Lay so bac (truong hop tong quat la 12 bac)
             string strSQL = "select id_bac, ten_bac from BAC";
             DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);

             //add vao datatable moi column tuong ung la moi bac luong
             for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
             {
                 myDataColumn = new DataColumn();
                 
                 //column name la ma bac luong
                 myDataColumn.DataType = System.Type.GetType("System.Double");
                 myDataColumn.ColumnName = ds.Tables[0].Rows[i][0].ToString();
                 //caption la ten bac luong
                 myDataColumn.Caption = ds.Tables[0].Rows[i][1].ToString();
                 myDataColumn.Unique = false;
                 myDataColumn.AllowDBNull = true;
                 myDataTable.Columns.Add(myDataColumn);
             }

             dsDataSet.Tables.Add(myDataTable);
         }
         catch (Exception ex) {
             throw ex;
         }
     }

        /// <summary>
        /// Format datagrid view
        /// </summary>
        public void FormatDataGridView()
        {
            BANGLUONGDataGridView.Columns["id_ngach"].Visible = false;

            BANGLUONGDataGridView.Columns["ten_ngach"].HeaderText = "Ngạch";
            BANGLUONGDataGridView.Columns["ten_ngach"].Frozen = true;
            BANGLUONGDataGridView.Columns["ten_ngach"].Width = 100;

            BANGLUONGDataGridView.Columns["maso"].HeaderText = "Mã số";
            BANGLUONGDataGridView.Columns["maso"].Frozen = true;
            BANGLUONGDataGridView.Columns["maso"].Width = 70;

            AddComboBoxColumns_NhomNgach();

            for (int i = FIRSTBAC; i < BANGLUONGDataGridView.Columns.Count; i++)
            {
                BANGLUONGDataGridView.Columns[i].HeaderText = "Bậc " + BANGLUONGDataGridView.Columns[i].HeaderText;
                BANGLUONGDataGridView.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                BANGLUONGDataGridView.Columns[i].Width = 70;
            }
        }

        public void AddNewRow()
        {
            DataRow dr = dsBangLuong.Tables[0].NewRow();
            dr["id_ngach"] = -1;
            dsBangLuong.Tables[0].Rows.Add(dr);
        }

        public void AddNewRow(int indexRow)
        {
            DataTable db = dsBangLuong.Tables[0].Copy();
            DataRow dr = db.Rows[indexRow];
            dr["id_ngach"] = -1;
            dsBangLuong.Tables[0].ImportRow(dr);
        }

        public void LoadDataGridView()
        {
            BANGLUONGDataGridView.DataSource = null;

            DataSet ds = null;

            //Lay so ngach
            string strSQL = "select id_ngach, maso, ten_ngach, id_nhomngach from NGACH";
            ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);

            //Create data table
            dsBangLuong = new DataSet();
            CreateDataSetDPNX(ref dsBangLuong);

            //Voi moi dong du lieu, ta lan luot add cac du lieu vao tung column
            for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
            {
                DataRow dr = dsBangLuong.Tables[0].NewRow();

                //column 1: add id ngach vao row
                dr["id_ngach"] = ds.Tables[0].Rows[row]["id_ngach"];
                //column 2: add ten ngach vao row
                dr["ten_ngach"] = ds.Tables[0].Rows[row]["ten_ngach"];
                //column 3: add ma so ngach vao row
                dr["maso"] = ds.Tables[0].Rows[row]["maso"];
                //column 4: add id_nhomngach vao row
                dr["id_nhomngach"] = ds.Tables[0].Rows[row]["id_nhomngach"];
                
                //Lay view danh muc Ngach bac luong
                strSQL = "select * from view_NBL where id_ngach = " + ds.Tables[0].Rows[row]["id_ngach"].ToString();
                DataTable db = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL).Tables[0];

                //doi voi cac column cua cac bac luong: 
                //add he so luong vao tung bac luong
                for (int i = 0; i < db.Rows.Count; i++)
                {
                    try
                    {
                        dr[db.Rows[i]["id_bac"].ToString()] = db.Rows[i]["heso"];
                    }
                    catch
                    {
                        dr[db.Rows[i]["id_bac"].ToString()] = 0;
                    }
                }
                dsBangLuong.Tables[0].Rows.Add(dr);
            }
            BANGLUONGDataGridView.DataSource = dsBangLuong.Tables[0].DefaultView;
            FormatDataGridView();
        }

        private void frmBangLuong_Load(object sender, EventArgs e)
        {
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet("select * from NHOMNGACH");
            cmbNhomNgach.DisplayMember = "ten_nhomngach";
            cmbNhomNgach.ValueMember = "id_nhomngach";
            cmbNhomNgach.DataSource = ds.Tables[0].DefaultView;
            

            luongcb = CTHAMSOCollection.GetGiaTriSo(CTHAMSOCollection.parLuongCB);
            txtLuongCB.Text = luongcb.ToString(GeneralCode.FormatMoneyStyle());

            //numericUpDownSoNam.Value = (decimal)CTHAMSOCollection.GetGiaTriSo(CTHAMSOCollection.parSNGB);
            //numericUpDownTNVK_1.Value = (decimal)CTHAMSOCollection.GetGiaTriSo(CTHAMSOCollection.parTNVK_1);
            //numericUpDownTNVK_2.Value = (decimal)CTHAMSOCollection.GetGiaTriSo(CTHAMSOCollection.parTNVK_2);

            LoadDataGridView();
        }

        private void btnThemMoi_Click(object sender, EventArgs e)
        {
            //CultureInfo c = new CultureInfo("en-US");
            //double double1 = double.Parse(chiffre1, c);
            //chiffre2 = double1.ToString(c.NumberFormat);
            
            //System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
            //double d1 = double.Parse("12.2");

            //System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("nb-NO");
            //double d2 = double.Parse("12,2");

            //StringBuilder sb = new StringBuilder();

            //// Loop through all the specific cultures known to the CLR.
            //foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.SpecificCultures))
            //{
            //    // Only show the currency symbols for cultures that speak English.
            //    if (ci.TwoLetterISOLanguageName != "en") continue;

            //    // Display the culture name and currency symbol.
            //    NumberFormatInfo nfi = ci.NumberFormat;
            //    sb.AppendFormat("The currency symbol for '{0}' is '{1}'",
            //        ci.DisplayName, nfi.CurrencySymbol);
            //    sb.AppendLine();
            //}
            //MessageBox.Show(sb.ToString());

            //AddNewRow();
        }

        private void btnChon_Click(object sender, EventArgs e)
        {
            if (BANGLUONGDataGridView.SelectedCells[0].ColumnIndex >= FIRSTBAC)
            {
                string strSQL;
                strSQL = "select id_NBL from view_NBL where ";
                strSQL += " id_ngach = " + BANGLUONGDataGridView.Rows[BANGLUONGDataGridView.SelectedCells[0].RowIndex].Cells[0].Value;
                strSQL += " and id_bac = " + BANGLUONGDataGridView.Columns[BANGLUONGDataGridView.SelectedCells[0].ColumnIndex].Name;

                frmNHANSU frm = (frmNHANSU)fCallForm;
                frm.HienThiNBL((int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL));
            }
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn chắc chắn muốn xóa Ngạch lương này?","Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
            {
                string strSQL;
                strSQL = "delete from NGACH where id_ngach = " + BANGLUONGDataGridView.Rows[BANGLUONGDataGridView.SelectedCells[0].RowIndex].Cells[0].Value;
                try
                {
                    MITI.BaseDB.Instance.ExecSql_NoneQuery(strSQL);
                    LoadDataGridView();
                    MessageBox.Show("Xóa thành công.");
                }
                catch
                {
                    MessageBox.Show("Xóa không thành công. Có người vẫn đang giữ Ngạch lương này.");
                }
            }
        }

        private void txtLuongCB_Leave(object sender, EventArgs e)
        {
            try
            {
                luongcb = int.Parse(txtLuongCB.Text);
                txtLuongCB.Text = luongcb.ToString(GeneralCode.FormatMoneyStyle());
            }
            catch
            {
                MessageBox.Show("Lương cơ bản không đúng");
                txtLuongCB.Focus();
            }
        }

        private void txtLuongCB_Enter(object sender, EventArgs e)
        {
            txtLuongCB.Text = luongcb.ToString();
        }

        private void thêmMớiHoànToànToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AddNewRow();
        }

        private void dựaTheoThôngTinHiệnTạiToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AddNewRow(BANGLUONGDataGridView.SelectedCells[0].RowIndex);
        }

        private void btnThemMoi_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                Point ClickPoint = new Point(e.X, e.Y);

                // Convert from Tree coordinates to Screen coordinates
                Point ScreenPoint = btnThemMoi.PointToScreen(ClickPoint);
                // Convert from Screen coordinates to Formc coordinates
                Point FormPoint = this.PointToClient(ScreenPoint);
                //Show context menu
                contextMenuStrip3.Show(this, FormPoint);
            }
        }

        private void BANGLUONGDataGridView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //if (e.ColumnIndex == BANGLUONGDataGridView.Columns["id_nhomngach"].Index)
            //{
            //    if (BANGLUONGDataGridView.Rows[e.RowIndex].Cells["id_nhomngach"].Value.ToString() == "2")
            //    {
            //        //reset value
            //        BANGLUONGDataGridView.Rows[e.RowIndex].Cells["id_nhomngach"].Value = DBNull.Value;
            //        return;
            //    }
            //}
        }

        private void cmbNhomNgach_SelectedIndexChanged(object sender, EventArgs e)
        {
            string strSQL = "select * from NHOMNGACH where id_nhomngach = " + cmbNhomNgach.SelectedValue.ToString();
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            numericUpDownSoNam.Value = decimal.Parse(ds.Tables[0].Rows[0]["sonamgiubac"].ToString());
            numericUpDownTNVK_1.Value = decimal.Parse(ds.Tables[0].Rows[0]["tnvk_1"].ToString());
            numericUpDownTNVK_2.Value = decimal.Parse(ds.Tables[0].Rows[0]["tnvk_2"].ToString());
        }
    }
}